﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace CAATS_Registration
{
    //数据连接对象，对MySQL进行了一次封装
    //注意一下，MySQL数据库在windows下是不区分列名大小写的
    public class DBConnection : IDisposable
    {
        //实际的MySQL连接对象
        private MySqlConnection _connection;

        //
        public static DBConnection Active { get; set; }

        //构造函数
        private DBConnection(string c_str)
        {
            _connection = new MySqlConnection(c_str);
        }

        //
        public string Server { get; set; }
        public string DBName { get; set; }
        public string User { get; set; }
        public string Password { get; set; }

        //通过服务器名称、数据库名称、数据库帐号与密码，创建出一个连接对象来
        public static DBConnection Open(string server, string dbname, string username, string password)
        {
            string c_str = string.Format("server={0};user={2};database={1};password={3};use compression=true", server, dbname, username, password);
            DBConnection c = new DBConnection(c_str) { Server = server, DBName = dbname, User = username, Password = password };
            if (c.Open())
            {
                return c;
            }
            return null;
        }

        //构造MySQL参数
        public static MySqlParameter MakeParameter(string name, MySqlDbType type, object value)
        {
            MySqlParameter p = new MySqlParameter(name, type);
            {
                p.Value = value;
            }
            return p;
        }

        //打开数据连接
        public bool Open()
        {
            try
            {
                _connection.Open();
                return true;
            }
            catch
            {
                //any
            }
            return false;
        }

        //释放资源
        public void Dispose()
        {
            if (_connection != null)
            {
                _connection.Dispose();
            }
        }

        //准备命令
        private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            //Set Connection
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //Set Command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = int.MaxValue;  //永不超时

            //Set Transaction
            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            //Set Param
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        //执行查询
        public int Execute(string sql, params MySqlParameter[] ps)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                //
                PrepareCommand(cmd, _connection, null, CommandType.Text, sql, ps);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                //
                return val;
            }
        }

        //标量查询
        public object Scalar(string sql, params MySqlParameter[] ps)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                //
                PrepareCommand(cmd, _connection, null, CommandType.Text, sql, ps);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();

                //
                return val;
            }
        }

        //Reader查询
        public MySqlDataReader Reader(string sql, params MySqlParameter[] ps)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                //
                PrepareCommand(cmd, _connection, null, CommandType.Text, sql, ps);
                MySqlDataReader val = cmd.ExecuteReader();
                cmd.Parameters.Clear();

                //
                return val;
            }
        }
    }
}
